#!/usr/bin/env bash
CURDIR=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
. $CURDIR/../shell_config.sh

${CLICKHOUSE_CLIENT} -nm --query 'CREATE DATABASE IF NOT EXISTS test'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS mv_block_number_agg_hit'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS mv_tx_from_address_hit'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS mv_tx_to_address_hit'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS polygon_tx3'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS block_number_agg_sum_hit_mv_target'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS tx_from_address_hit_mv_target'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS tx_to_address_hit_mv_target'

${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE TABLE polygon_tx3 (type LowCardinality(UInt8), status LowCardinality(UInt64), block_hash String, block_number UInt64, block_timestamp DateTime, transaction_hash String, transaction_index UInt32, from_address String, to_address String, value String, input String, nonce UInt64, contract_address String, gas UInt64, gas_price UInt64, gas_used UInt64, effective_gas_price UInt64, cumulative_gas_used UInt64, max_fee_per_gas UInt64, max_priority_fee_per_gas UInt64, r LowCardinality(String), s LowCardinality(String), v UInt64, logs_count UInt32) ENGINE = CnchMergeTree PARTITION BY toDate(toStartOfMonth(block_timestamp)) PRIMARY KEY block_number ORDER BY block_number SETTINGS index_granularity = 8192'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE TABLE block_number_agg_sum_hit_mv_target (block_number UInt64, month_date Date, gas_fee AggregateFunction(sum, UInt64)) ENGINE = CnchAggregatingMergeTree() PARTITION BY month_date ORDER BY (block_number, month_date) SETTINGS index_granularity = 4096'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE TABLE tx_from_address_hit_mv_target (type LowCardinality(UInt8), status LowCardinality(UInt64), block_hash String, block_number UInt64, block_timestamp DateTime, transaction_hash String, transaction_index UInt32, from_address String, to_address String, value String, input String, nonce UInt64, contract_address String, gas UInt64, gas_price UInt64, gas_used UInt64, effective_gas_price UInt64, cumulative_gas_used UInt64, max_fee_per_gas UInt64, max_priority_fee_per_gas UInt64, r LowCardinality(String), s LowCardinality(String), v UInt64, logs_count UInt32) ENGINE = CnchMergeTree PARTITION BY toDate(toStartOfMonth(block_timestamp)) PRIMARY KEY from_address ORDER BY from_address SETTINGS index_granularity = 8192'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE TABLE tx_to_address_hit_mv_target (type LowCardinality(UInt8), status LowCardinality(UInt64), block_hash String, block_number UInt64, block_timestamp DateTime, transaction_hash String, transaction_index UInt32, from_address String, to_address String, value String, input String, nonce UInt64, contract_address String, gas UInt64, gas_price UInt64, gas_used UInt64, effective_gas_price UInt64, cumulative_gas_used UInt64, max_fee_per_gas UInt64, max_priority_fee_per_gas UInt64, r LowCardinality(String), s LowCardinality(String), v UInt64, logs_count UInt32) ENGINE = CnchMergeTree PARTITION BY toDate(toStartOfMonth(block_timestamp)) PRIMARY KEY to_address ORDER BY to_address SETTINGS index_granularity = 8192'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE MATERIALIZED VIEW mv_block_number_agg_hit TO block_number_agg_sum_hit_mv_target (block_number UInt64, month_date Date, gas_fee AggregateFunction(sum, UInt64)) AS SELECT block_number, toDate(toStartOfMonth(block_timestamp)) AS month_date, sumState(gas * gas_price) AS gas_fee FROM polygon_tx3  WHERE (block_number > 10000000) AND (block_number < 20000000) GROUP BY block_number, toDate(toStartOfMonth(block_timestamp))'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE MATERIALIZED VIEW mv_tx_from_address_hit TO tx_from_address_hit_mv_target (type LowCardinality(UInt8), status LowCardinality(UInt64), block_hash String, block_number UInt64, block_timestamp DateTime, transaction_hash String, transaction_index UInt32, from_address String, to_address String, value String, input String, nonce UInt64, contract_address String, gas UInt64, gas_price UInt64, gas_used UInt64, effective_gas_price UInt64, cumulative_gas_used UInt64, max_fee_per_gas UInt64, max_priority_fee_per_gas UInt64, r LowCardinality(String), s LowCardinality(String), v UInt64, logs_count UInt32) AS SELECT * FROM polygon_tx3'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE MATERIALIZED VIEW mv_tx_to_address_hit TO tx_to_address_hit_mv_target (type LowCardinality(UInt8), status LowCardinality(UInt64), block_hash String, block_number UInt64, block_timestamp DateTime, transaction_hash String, transaction_index UInt32, from_address String, to_address String, value String, input String, nonce UInt64, contract_address String, gas UInt64, gas_price UInt64, gas_used UInt64, effective_gas_price UInt64, cumulative_gas_used UInt64, max_fee_per_gas UInt64, max_priority_fee_per_gas UInt64, r LowCardinality(String), s LowCardinality(String), v UInt64, logs_count UInt32) AS SELECT * FROM polygon_tx3'

${CLICKHOUSE_CLIENT} -nm --query "USE test; INSERT INTO polygon_tx3(block_number, from_address, to_address) VALUES (1, '0xdb03b11f5666d0e51934b43bd830ff070497ff8c', '0xdb03b11f5666d0e51934b43bd830ff070497ff8c') (1, '0xdb03b11f5666d0e51934b43bd830ff070497ff8c', '0xdb03b11f5666d0e51934b43bd830ff070497ff8c') (1, '0xdb03b11f5666d0e51934b43bd830ff070497ff8c', '0xdb03b11f5666d0e51934b43bd830ff070497ff8c') (2, '0xdb03b11f5666d0e51934b43bd830ff070497ff8c', '0xdb03b11f5666d0e51934b43bd830ff070497ff8c') (2, '0xdb03b11f5666d0e51934b43bd830ff070497ff8c', '0xdb03b11f5666d0e51934b43bd830ff070497ff8c') (2, '0xdb03b11f5666d0e51934b43bd830ff070497ff8c', '0xdb03b11f5666d0e51934b43bd830ff070497ff8c')"

${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE TABLE_STATS polygon_tx3' >/dev/null
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE TABLE_STATS block_number_agg_sum_hit_mv_target' >/dev/null
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE TABLE_STATS tx_from_address_hit_mv_target' >/dev/null
${CLICKHOUSE_CLIENT} -nm --query 'USE test; CREATE TABLE_STATS tx_to_address_hit_mv_target' >/dev/null

${CLICKHOUSE_CLIENT} -nm --query "USE test; SET enable_optimizer=1, enable_create_topn_filtering_for_aggregating=1, enable_materialized_view_rewrite=1, enforce_materialized_view_rewrite=1, materialized_view_consistency_check_method='NONE'; select block_number, sum(gas * gas_price) as gas_fee from polygon_tx3 where block_number > 10000000 and block_number < 20000000 group by block_number order by block_number limit 10"
${CLICKHOUSE_CLIENT} -nm --query "USE test; SET enable_optimizer=1, enable_create_topn_filtering_for_aggregating=1, enable_materialized_view_rewrite=1, enforce_materialized_view_rewrite=1, materialized_view_consistency_check_method='NONE'; SELECT block_number, transaction_hash, substring(input, 1, 8) AS func_sign FROM ( SELECT block_number, transaction_index, transaction_hash, input FROM polygon_tx3 WHERE from_address = '0xdb03b11f5666d0e51934b43bd830ff070497ff8c' ORDER BY block_number DESC, transaction_index DESC UNION ALL SELECT block_number, transaction_index, transaction_hash, input FROM polygon_tx3 WHERE to_address = '0xdb03b11f5666d0e51934b43bd830ff070497ff8c' ORDER BY block_number DESC, transaction_index DESC) ORDER BY block_number DESC, transaction_index DESC LIMIT 100"

${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS mv_block_number_agg_hit'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS mv_tx_from_address_hit'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS mv_tx_to_address_hit'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS polygon_tx3'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS block_number_agg_sum_hit_mv_target'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS tx_from_address_hit_mv_target'
${CLICKHOUSE_CLIENT} -nm --query 'USE test; DROP TABLE IF EXISTS tx_to_address_hit_mv_target'
